-- 抓取紫金山、七星潭 留言板中匹配到关键词的运单, 并返回匹配到了哪些关键词
-- 主键: 运单 + 更新时间
with massage as (
    select 
        waybill_no
        , reply_content
        , update_time
        , register_network_code  -- 登记网点编码
        , register_network_name  -- 登记网点名称
    from 
        jms_dwd.dwd_sqs_leave_message_base_dt -- 留言板
    where 
        dt = '{{ execution_date | cst_ds }}'
        and (
            reply_content regexp '取消|撤销|重新|派|续'              -- 七星潭过滤需求
            or  reply_content regexp '微信|理赔|线下|联系|遗失'      -- 七星潭&紫金山过滤需求  
        )
)
insert overwrite table jms_dm.dm_leave_message_keywords_detail_dt partition(dt = '{{ execution_date | cst_ds }}')
select 
     massage.waybill_no
    ,massage.update_time
    ,waybill.ordersource_code
    ,waybill.ordersource_name
    ,massage.register_network_code 
    ,massage.register_network_name 
    ,case when waybill.ordersource_name = '七星潭' then 
                                                        substr(
                                                            concat(
                                                                  if(reply_content regexp '取消', concat(',', regexp_extract(reply_content, '取消',0)) ,'')
                                                                , if(reply_content regexp '撤销', concat(',', regexp_extract(reply_content, '撤销',0)) ,'')
                                                                , if(reply_content regexp '重新', concat(',', regexp_extract(reply_content, '重新',0)) ,'')
                                                                , if(reply_content regexp '派',   concat(',', regexp_extract(reply_content, '派'  ,0)) ,'')
                                                                , if(reply_content regexp '续',   concat(',', regexp_extract(reply_content, '续'  ,0)) ,'')
                                                                , if(reply_content regexp '微信', concat(',', regexp_extract(reply_content, '微信',0)) ,'')
                                                                , if(reply_content regexp '理赔', concat(',', regexp_extract(reply_content, '理赔',0)) ,'')
                                                                , if(reply_content regexp '线下', concat(',', regexp_extract(reply_content, '线下',0)) ,'')
                                                                , if(reply_content regexp '联系', concat(',', regexp_extract(reply_content, '联系',0)) ,'')
                                                                , if(reply_content regexp '遗失', concat(',', regexp_extract(reply_content, '遗失',0)) ,'')
                                                            )
                                                        ,2)
         when waybill.ordersource_name = '紫金山' then 
                                                        substr(
                                                            concat(
                                                                  if(reply_content regexp '微信', concat(',', regexp_extract(reply_content, '微信',0)) ,'')
                                                                , if(reply_content regexp '理赔', concat(',', regexp_extract(reply_content, '理赔',0)) ,'')
                                                                , if(reply_content regexp '线下', concat(',', regexp_extract(reply_content, '线下',0)) ,'')
                                                                , if(reply_content regexp '联系', concat(',', regexp_extract(reply_content, '联系',0)) ,'')
                                                                , if(reply_content regexp '遗失', concat(',', regexp_extract(reply_content, '遗失',0)) ,'')
                                                            )
                                                        ,2) 
    end     as  message_keywords
from 
    massage
left join 
    jms_dwd.dwd_yl_oms_oms_waybill_incre_dt waybill -- 运单
        on massage.waybill_no = waybill.waybill_no
           and waybill.dt between date_sub('{{ execution_date | cst_ds }}', 30) and '{{ execution_date | cst_ds }}'
           and ordersource_name in ('七星潭', '紫金山')
where 
    waybill.ordersource_name in ('七星潭', '紫金山')
    and case  
            when waybill.ordersource_name = '七星潭' then reply_content regexp '取消|撤销|重新|派|续|微信|理赔|线下|联系|遗失'
            when waybill.ordersource_name = '紫金山' then reply_content regexp '微信|理赔|线下|联系|遗失'
        end
distribute by 1
;
